% Cohort Transprob cacluation with conditions:
% Rates {1+1.5, 2,2.5,3,3.5,4, 4.5+5+5.5, -1};
% maxPPDOS_sum constrains is controlled by variable 'conditions' in loop
% industry = ''Manufacture''
% cdIdex
% Quarterly: we add variable NUMBER_OF_Quarter and varialbles change to 4-dimension.

clear all
clc

conn = database('BDC','','');
labels = {1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,-1};
edges = [2 3 4 5 6 7 10 11 12];
%edgesDur = [2 3 4 5 6 7 10 11];

year_list=1997:2009;
quarter_list=[12,3,6,9,12];
DURATION = 1;
NUMBER_OF_YEAR = length(year_list) - DURATION;
NUMBER_OF_QTR = length(quarter_list)-DURATION;


conditions = {'maxPPDOS_sum >= 50000','maxPPDOS_sum >= 50000 and maxPPDOS_sum < 250000',...
    'maxPPDOS_sum >= 250000 and maxPPDOS_sum <1000000','maxPPDOS_sum >= 1000000'};
numRate = length(labels)+1; % add column for WR group
numEdges = length(edges);
numEdgesStd = numEdges-1;
numGrp = length(conditions);

coh = zeros(numEdgesStd, numEdgesStd);
cohTp = zeros(numGrp, NUMBER_OF_YEAR, NUMBER_OF_QTR, numRate, numRate);
cohTpGrp = zeros(numGrp, NUMBER_OF_YEAR, NUMBER_OF_QTR, numEdges, numEdges);
cohTpGrpStd =zeros(numGrp, NUMBER_OF_YEAR, NUMBER_OF_QTR, numEdgesStd, numEdgesStd);
cohTtlStdVec = zeros(numGrp, NUMBER_OF_YEAR, NUMBER_OF_QTR, 1, numEdgesStd);
cohTtlStdMat = zeros(numGrp, NUMBER_OF_YEAR, NUMBER_OF_QTR, numEdgesStd, numEdgesStd);
SVD = zeros(NUMBER_OF_YEAR,NUMBER_OF_QTR, numGrp);
PD = zeros (NUMBER_OF_YEAR, NUMBER_OF_QTR, numGrp);

for cdIdx = 1:numGrp
    disp(conditions(cdIdx));
    condition = char(conditions(cdIdx));
    
    for yearIdx=1:NUMBER_OF_YEAR
        for qtrIdx=1:NUMBER_OF_QTR
            
        year1 = year_list(yearIdx)-floor(quarter_list(qtrIdx) / 12)+1;
        year2 = year1 + floor(quarter_list(qtrIdx) / 12);
               
        Date1 = strcat(num2str(year1),'-', num2str(quarter_list(qtrIdx)),'-01');
        Date2 = strcat(num2str(year2),'-', num2str(quarter_list(qtrIdx+DURATION)),'-01');
        disp( strcat(['Date: ' Date1 ' to ' Date2]));      
 
        sqlquery1 = strcat(['select Customer, modiFisDate, modiRate FROM final_v4_tbl where ' condition ' and industry = ''Manufacture'' and modiFisDate BETWEEN ''' Date1 ''' and ''' Date2 ''' order by customer, modiFisDate']);
        results = fetch(conn, sqlquery1);
        disp(size(results));
    
        
        % cohort
        % we cannot use function 'transprob' because it doesn't consider the WR situation.
        [cohTp(cdIdx,yearIdx,qtrIdx,:,:),ttl] = transprobwr(results, 'labels',labels,'algorithm','cohort','snapsPerYear',4);
     
        TpGrp = transprobgrouptotals(ttl,edges);
        cohTpGrp(cdIdx,yearIdx,qtrIdx,:,:) = transprobbytotalswr(TpGrp);
               
        TpGrpStd.totalsVec = sum(TpGrp.totalsMat(1:numEdgesStd,1:numEdgesStd),2)';
        TpGrpStd.totalsMat = TpGrp.totalsMat(1:numEdgesStd,1:numEdgesStd);
        TpGrpStd.algorithm = TpGrp.algorithm;
        cohTpGrpStd(cdIdx,yearIdx,qtrIdx,:,:) = transprobbytotals(TpGrpStd);
        cohTtlStdVec (cdIdx,yearIdx,qtrIdx,1,:) = TpGrpStd.totalsVec;
        cohTtlStdMat (cdIdx,yearIdx,qtrIdx,:,:) = TpGrpStd.totalsMat;  
        disp('Cohort tranprob is done')  
        
        % SVD & PD
        coh (:,:) =  cohTpGrpStd(cdIdx, yearIdx,qtrIdx, :,:)./100;
        SVD(yearIdx, qtrIdx, cdIdx) = mean(svd(coh-eye(numEdgesStd)));
        Mat(:,:) = cohTtlStdMat (cdIdx,yearIdx,qtrIdx,:,:);
        PD(yearIdx, qtrIdx, cdIdx)= sum(Mat(:,numEdgesStd)) / sum(sum(Mat));
            
        disp('SVD & PD is done')  
        end
    end
end

save ('D:\My Documents\CIRANO\db_mySQL\tp_qtr_sector_manufacture.mat')


% figure SVD & PD

% Y = cellstr(char('1998','1999','2000','2001','2002','2003','2004','2005','2006','2007','2008','2009'));
% R = cellstr(char('1.5','2','2.5','3','3.5','4','4.5'));
SG = cellstr(char('Total sample: [$50k,+)','Subsample A: [$50k, 250k)','Subsample B: [$250k, 1000k)','Subsample C: [$1000k, +)'));
PD_ts = zeros(NUMBER_OF_YEAR*NUMBER_OF_QTR,numGrp);
SVD_ts = zeros(NUMBER_OF_YEAR*NUMBER_OF_QTR,numGrp);

for cdIdx = 1:numGrp  
    for yearIdx=1:NUMBER_OF_YEAR
        for qtrIdx=1:NUMBER_OF_QTR
            
            PD_ts((yearIdx-1)*4+qtrIdx,cdIdx) = PD(yearIdx,qtrIdx,cdIdx);
            SVD_ts((yearIdx-1)*4+qtrIdx,cdIdx) = SVD(yearIdx,qtrIdx,cdIdx);
            
        end
    end
end

figure;
plot (PD_ts);
set(gca,'XTick',1:4:size(PD_ts,1));
qlabels = quaterlabels(1998, size(PD_ts,1));
qlabels = qlabels(1:4:size(PD_ts,1));
set(gca,'XTickLabel',qlabels);
grid minor;
xlabel('Quarters');
ylabel('Default Frequency');
legend(SG,'Location','NorthWest');

figure;
plot (SVD_ts);
set(gca,'XTick',1:4:size(SVD_ts,1));
qlabels = quaterlabels(1998, size(SVD_ts,1));
qlabels = qlabels(1:4:size(SVD_ts,1));
set(gca,'XTickLabel',qlabels);
grid minor;
xlabel('Quarters');
ylabel('SVD');
legend(SG,'Location','SouthWest');

% draw the graph of cell prob along time
% We did the same cell as paper Xing (2010)
% ------------------
% 1 AAA 1.5
% 2 AA  2
% 3 A   2.5
% 4 BBB 3
% 5 BB  3.5
% 6 B   4
% 7 C   4.5
% 8 D   D
%-------------------
load 'D:\My Documents\CIRANO\db_mySQL\tp_qtr_sector_manufacture.mat';
stdProbQ = zeros (numGrp, NUMBER_OF_YEAR * NUMBER_OF_QTR, numEdgesStd, numEdgesStd);

for cdIdx = 1:numGrp  
    for yearIdx=1:NUMBER_OF_YEAR
        for qtrIdx=1:NUMBER_OF_QTR     
            
            stdProbQ (cdIdx,(yearIdx-1)*4+qtrIdx,:,:)= cohTpGrpStd(cdIdx,yearIdx,qtrIdx,:,:);

        end
    end
end

cell_labels = {'1.5','2','2.5','3','3.5','4','4.5','D'};

save ('D:\My Documents\CIRANO\db_mySQL\tp_qtr_sector_manufacture_fin.mat')


% only group 4 (>1000k)
% check rating 3 to other ratings.
figure; 
for i = 1:numEdgesStd
subplot(4,2,i); plot (stdProbQ(4,:,4,i)); 
legend(['Rating 3 to ' cell_labels{i}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
end
title('Observed Transition Prob along Time for Rating 3',... 
  'FontWeight','bold')

% the cell to check include: 1-1, 5-5, 1-8, 5-8, 2-6, 3-4

figure; 
subplot(3,2,1); plot (stdProbQ(4,:,1,1)); 
legend(['Rating 1.5 to ' cell_labels{1}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
subplot(3,2,2); plot (stdProbQ(4,:,5,5)); 
legend(['Rating 3.5 to ' cell_labels{5}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
subplot(3,2,3); plot (stdProbQ(4,:,1,8)); 
legend(['Rating 1.5 to ' cell_labels{8}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
subplot(3,2,4); plot (stdProbQ(4,:,5,8)); 
legend(['Rating 3.5 to ' cell_labels{8}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
subplot(3,2,5); plot (stdProbQ(4,:,2,6)); 
legend(['Rating 2 to ' cell_labels{6}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
subplot(3,2,6); plot (stdProbQ(4,:,3,4)); 
legend(['Rating 2.5 to ' cell_labels{4}],'Location','NorthWest');
set(gca,'XTick',1:4:size(stdProbQ,2));
qlabels = quaterlabels(1998, size(stdProbQ,2));
qlabels = qlabels(1:4:size(stdProbQ,2));
set(gca,'XTickLabel',qlabels);
xlabel('Quarter');
ylabel('TransProb %');
grid on
title('Observed Transition Prob along Time',... 
  'FontWeight','bold')
